/**  
 * All rights Reserved
 * @Title:  ExportExcelByTemplateUtil.java   
 * @Package com.qunzhi.base   
 * @Description:    TODO(用一句话描述该文件做什么)   
 * @author: 罗嘉甫    
 * @date:   2019年5月29日 下午1:53:19   
 * @Copyright: 2019 Inc. All rights reserved. 
 */
package net.qunzhi.common.utils;

import net.qunzhi.common.vo.ExcelDataVO;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.ResourceUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**   
 * @ClassName:  ExportExcelByTemplateUtil   
 * @Description:根据模板导出excel，模板放   static/doc下，服务器放File/templates下
 * @author: 罗嘉甫
 * @date:   2019年5月29日 下午1:53:19     
 * @Copyright: 2019 All rights reserved.  
 */
public class ExportExcelByTemplateUtil {
	
	 public static String LINUX_PATH = "/file/templates";
	 public static String UPLOAD_PATH = "/file/xls";

	 public static String getPathBySystem(String template) throws Exception {
		 
		 String os = System.getProperty("os.name");
			if (os.toLowerCase().startsWith("win")) {
				String serverpath= ResourceUtils.getURL("classpath:static").getPath().replace("%20"," ");
				String path_prefix=serverpath.substring(1);//从路径字符串中取出工程路径
				return path_prefix+"/doc/"+template;
			} else {
				return LINUX_PATH+"/"+template;
			}
	 }
	/**
     * 
     * @Title: exportExcelCustom   
     * @Description: 导入数据，并按模版输出格式
     *  @param response
     *  @param data	数据列表
     *  @param rowIndex	插入第几行
     *  @param fileName	导出文件名称
     *  @throws Exception
     * @return: void      
     * @throws
     */
    public static void exportExcelCustom(HttpServletResponse response, String template, ExcelDataVO data, int rowIndex, String fileName) throws Exception {
        //response.setContentType("bin");
        //response.setContentType("application/octet-stream");
        //response.setContentType("application/vnd.ms-excel");
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));//默认Excel名称
        response.flushBuffer();
        
        createExcel(data, response.getOutputStream(),getPathBySystem(template),rowIndex);
    }
    
    public static void createExcel(ExcelDataVO data, OutputStream output, String path, int rowIndex) {

    	HSSFWorkbook tempWorkBook = null;
        Sheet tempSheet = null;
        InputStream inputstream = null;
        try {
            
        	File file=new File(path);
            FileInputStream fileInputStream=new FileInputStream(file);
            inputstream=fileInputStream;
            // 获取模板
            tempWorkBook = new HSSFWorkbook(inputstream);
            // 获取模板sheet页
            tempSheet = tempWorkBook.getSheetAt(0);
            
            HSSFCellStyle dataStyle = tempWorkBook.createCellStyle();
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 自动换行  
            dataStyle.setWrapText(true);
            dataStyle.setBorderBottom(BorderStyle.THIN); //下边框
            dataStyle.setBorderLeft(BorderStyle.THIN);//左边框
            dataStyle.setBorderTop(BorderStyle.THIN);//上边框
            dataStyle.setBorderRight(BorderStyle.THIN);//右边框
            
            // 将数据写入excel
            for(Map<String, Object> map : data.getRows()){
            	Row dataRow = tempSheet.createRow(rowIndex);
            	dataRow.setHeightInPoints(1000);
            	dataRow.setHeight((short) 1000);
            	int valueN = 0;
               	for(String RowValue : data.getRowsName()){
                    if (map.containsKey(RowValue)) {
                        Object value = map.get(RowValue);

                        if (value == null) {
                            value = "";
                        }

                        Class<?> type = value.getClass();
                        Cell cell = dataRow.createCell(valueN);
                        if(type == LocalDateTime.class) {
                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                            cell.setCellValue(((LocalDateTime) value).format(formatter));
                        } else if(type == LocalDate.class) {
                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                            cell.setCellValue(((LocalDate) value).format(formatter));
                        } else if(type == Boolean.class) {
                            cell.setCellValue((Boolean)value?"是":"否");
                        } else {
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellStyle(dataStyle);
                    }
                    valueN++;
                }
               	rowIndex++;
            }
            
            // 将内容写入Excel
            tempWorkBook.write(output);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                output.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void createExcelForUpload(ExcelDataVO data, String path, int rowIndex, String savePath, String yearMonth) {

    	HSSFWorkbook tempWorkBook = null;
        Sheet tempSheet = null;
        InputStream inputstream = null;
        FileOutputStream fileOutputStream = null;
        try {
        	File file=new File(getPathBySystem(path));
            FileInputStream fileInputStream=new FileInputStream(file);
            inputstream=fileInputStream;
            // 获取模板
            tempWorkBook = new HSSFWorkbook(inputstream);
            // 获取模板sheet页
            tempSheet = tempWorkBook.getSheetAt(0);

            HSSFCellStyle dataStyle = tempWorkBook.createCellStyle();
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 自动换行
            dataStyle.setWrapText(true);
            dataStyle.setBorderBottom(BorderStyle.THIN); //下边框
            dataStyle.setBorderLeft(BorderStyle.THIN);//左边框
            dataStyle.setBorderTop(BorderStyle.THIN);//上边框
            dataStyle.setBorderRight(BorderStyle.THIN);//右边框

            // 将数据写入excel
            for(Map<String, Object> map : data.getRows()){
            	Row dataRow = tempSheet.createRow(rowIndex);
            	dataRow.setHeightInPoints(1000);
            	dataRow.setHeight((short) 1000);
            	int valueN = 0;
               	for(String RowValue : data.getRowsName()){
                    if (map.containsKey(RowValue)) {
                        Object value = map.get(RowValue);

                        if (value == null) {
                            value = "";
                        }

                        Class<?> type = value.getClass();
                        Cell cell = dataRow.createCell(valueN);
                        if(type == LocalDateTime.class) {
                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                            cell.setCellValue(((LocalDateTime) value).format(formatter));
                        } else if(type == LocalDate.class) {
                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                            cell.setCellValue(((LocalDate) value).format(formatter));
                        } else if(type == Boolean.class) {
                            cell.setCellValue((Boolean)value?"是":"否");
                        } else {
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellStyle(dataStyle);
                    }
                    valueN++;
                }
               	rowIndex++;
            }
            // 将内容写入Excel
//            tempWorkBook.write(output);
            FileUtil.createDir(savePath);
            fileOutputStream = new FileOutputStream(savePath+"/"+yearMonth+".xls");
//            fileOutputStream = new FileOutputStream(UPLOAD_PATH+"/"+yearMonth+".xls");
            tempWorkBook.write(fileOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(null!=fileOutputStream){
                fileOutputStream.close();
                 }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
//    public static List<Map<String, Object>> getRows(List<?> list) {
//        List<Map<String, Object>> ListMap = new ArrayList<>();
//        for (Iterator it2 = list.iterator(); it2.hasNext(); ) {
//            ListMap.add(ArithUtil.objectToMap(it2.next()));
//        }
//        return ListMap;
//    }
}
